﻿using ACC.Extensions.Entities;
using ACC.Extensions.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ACC.Extensions.ClientBusiness
{
    public class ClientSqlLite
    {
        public readonly string strDBpath = AppDomain.CurrentDomain.BaseDirectory + "_data\\ACCPcClientDB.db3";
        public ClientSqlLite()
        {
            SqlLiteHelper.SetConnectionString(strDBpath, "");
        }
        //删除数据SqlLite
        public void DeleteBarcode(string strBarcode)
        {
            string strSql = string.Format("Delete From BarcodeList where Barcode = '{0}'", strBarcode);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
        }
        public void DeleteBarcode(List<string> listBarcode)
        {
            string strBarcodes = "";
            foreach (string item in listBarcode)
            {
                strBarcodes = strBarcodes + "'" + item + "',";
            }
            strBarcodes = strBarcodes.Substring(0, strBarcodes.Length - 1);
            string strSql = string.Format("Delete From BarcodeList where Barcode in ({0})", strBarcodes);
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
        }

        public void DeleteBarcodeAll()
        {
            string strSql = "Delete From BarcodeList";
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
        }
         
        //插入数据到SqlLite
        public void InsertBarcode(BarcodeInfo bi, string strStatus)
        {
            string strSql = string.Format("Delete From BarcodeList where Barcode = '{0}'", bi.Barcode);

            strSql = strSql + ";" + string.Format("Insert into BarcodeList(ScanNo,Barcode,CodeLevel,Type,PartNo,State,Status,RequireQty,DateTime) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                bi.ScanNo == null ? 0 : bi.ScanNo, bi.Barcode, bi.CodeLevel == null ? "" : bi.CodeLevel, bi.Type, bi.PartNo, bi.State, strStatus,bi.RequireQty, bi.DateTime.ToString("s"));
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
        }

        public void InsertBarcode2(string code, string strStatus)
        {
            string strSql = string.Format("Insert into BarcodeList(ScanNo,Barcode,CodeLevel,Type,PartNo,State,Status,RequireQty,DateTime) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                0, code, "", "", "", "", strStatus, "", DateTime.Now.ToString("s"));
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
        }

        public DataTable GetBarcode(string strStatus)
        {
            string strSql = string.Format("select ScanNo, Barcode,CodeLevel,PartNo,Type,State,DateTime from BarcodeList where Status='{0}' order by RequireQty ASC, DateTime ASC", strStatus);
            //string strSql = string.Format("select ScanNo, Barcode,CodeLevel,PartNo,Type,State,DateTime from BarcodeList where Status='{0}' order by DateTime ASC", strStatus);
            Log4Helper.Info(strSql);
            DataTable dt = SqlLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[0]);
            return dt;
        }

        public bool CheckBarcodeExist(string strBarcode, string strStatus)
        {
            bool bExist = false;
            string strSql = string.Format("select ScanNo, Barcode,CodeLevel,PartNo,Type,State,DateTime from BarcodeList where Status='{0}' and Barcode = '{1}' order by DateTime ASC", strStatus, strBarcode);
            Log4Helper.Info(strSql);
            DataTable dt = SqlLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[0]);
            if (dt != null && dt.Rows.Count > 0)
            {
                bExist = true;
            }
            return bExist;
        }

        public DataTable GetTestBarcode()
        {
            //string strSql = string.Format("select ScanNo, Barcode,CodeLevel,PartNo,Type,State,DateTime from BarcodeList where Status='{0}' order by RequireQty ASC, DateTime ASC", strStatus);
            string strSql = string.Format("select ScanNo, Barcode,CodeLevel,PartNo,Type,State,DateTime from BarcodeList where State='{0}' order by DateTime ASC", "TEST");
            Log4Helper.Info(strSql);
            DataTable dt = SqlLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[0]);
            return dt;
        }

        public DataTable GetBarcodeNOK()
        {
            string strSql = string.Format("select DateTime, Barcode,PartNo,Type from BarcodeList where Status='NOK' order by RequireQty ASC, DateTime ASC");
            //string strSql = string.Format("select DateTime, Barcode,PartNo,Type from BarcodeList where Status='NOK' order by DateTime ASC");
            Log4Helper.Info(strSql);
            DataTable dt = SqlLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[0]);
            return dt;
        }
        public DataTable GetFirstBarcode(string strStatus)
        {
            string strSql = string.Format("select Barcode  from BarcodeList where Status='{0}' order by RequireQty ASC, DateTime ASC limit 0,1 ", strStatus);
            Log4Helper.Info(strSql);
            DataTable dt = SqlLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[0]);
            return dt;
        }


        public void UniformInsertBarcode(string Area, string PrintLable, string ReaderLable, string CodeLevel)
        {
            string strSql = string.Format("Delete From UniformBarcode where Area = '{0}'", Area);

            strSql = strSql + ";" + string.Format("Insert into UniformBarcode(Area,PrintLable,ReaderLable,CodeLevel) values ('{0}','{1}','{2}','{3}')",
                Area, PrintLable,  ReaderLable, CodeLevel);
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);

            UniformRefreshStatus();
        }

        private void UniformRefreshStatus()
        {
            string strSql = "Update UniformBarcode Set Status=0 where  ifnull( PrintLable,'')='' or ifnull( ReaderLable,'')='' ";
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);

            strSql = "Update UniformBarcode Set Status=1 where PrintLable = ReaderLable and (ifnull( PrintLable,'')<>'' or ifnull( ReaderLable,'')<>'')";
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);

            strSql = "Update UniformBarcode Set Status=2 where PrintLable <> ReaderLable and (ifnull( PrintLable,'')<>'' or ifnull( ReaderLable,'')<>'')";
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
        }
        public void UniformDeleteAllBarcode()
        {
            string strSql = "Delete From UniformBarcode";
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
        }

        public void UniformDeleteBarcodeByArea( string area)
        {
            if (!string.IsNullOrEmpty(area))
            {
                string strSql = "Delete From UniformBarcode Where Area='" + area + "'";
                Log4Helper.Info(strSql);
                SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
            }
        }
        public DataTable UniformGetAllBarcode()
        {
            string strSql = "Select * From UniformBarcode";
            Log4Helper.Info(strSql);
            DataTable dt = SqlLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[0]);
            return dt;
        }

        public void UniformUpdateBarcode(string Area, string PrintLable, string ReaderLable, string CodeLevel)
        {
            string strSql = "Update UniformBarcode Set "; 
            if (!string.IsNullOrEmpty( PrintLable))
            {
                strSql = strSql + "PrintLable ='" + PrintLable + "', ";
            }
            if (!string.IsNullOrEmpty(ReaderLable))
            {
                strSql = strSql + "ReaderLable ='" + ReaderLable + "', ";
            }
            if (!string.IsNullOrEmpty(CodeLevel))
            {
                strSql = strSql + "CodeLevel ='" + CodeLevel + "', ";
            }
            if (strSql == "Update UniformBarcode Set ")
            {
                return;
            }
            strSql = strSql.Substring(0, strSql.Length - 2);
            
            strSql = strSql + " where Area='" + Area + "'";
            Log4Helper.Info(strSql);
            SqlLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[0]);
            UniformRefreshStatus();
        }

        public bool UniformUpdateCheckBarcode(string Area)
        {
            string strSql = string.Format("select  1 from UniformBarcode where Area='{0}' and PrintLable=ReaderLable ", Area.ToString());
            Log4Helper.Info(strSql);
            DataTable dt = SqlLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[0]);
            if (dt.Rows.Count>0)
            {
                return true;
            }
            else
            {
                return false;
            }


        }
    }
}
